<?php

/**
 * Classe d'accès aux données. 

 * Utilise les services de la classe PDO
 * pour l'application GSB
 * Les attributs sont tous statiques,
 * les 4 premiers pour la connexion
 * $monPdo de type PDO 
 * $monPdoInstance qui contiendra l'unique instance de la classe
 */
class PdoGsb {

    private static $serveur = 'mysql:host=localhost';
    private static $bdd = 'dbname=projet_web_gestion_tickets_incidents';
    private static $user = 'root';
    private static $mdp = '';
    private static $monPdo;
    private static $monPdoGsb = null;

    /**
     * Constructeur privé, crée l'instance de PDO qui sera sollicitée
     * pour toutes les méthodes de la classe
     */
    private function __construct() {
        PdoGsb::$monPdo = new PDO(PdoGsb::$serveur . ';' . PdoGsb::$bdd, PdoGsb::$user, PdoGsb::$mdp);
        PdoGsb::$monPdo->query("SET CHARACTER SET utf8");
    }

    public function _destruct() {
        PdoGsb::$monPdo = null;
    }

    /**
     * Fonction statique qui crée l'unique instance de la classe

     * Appel : $instancePdoGsb = PdoGsb::getPdoGsb();

     * @return l'unique objet de la classe PdoGsb
     */
    public static function getPdoGsb() {
        if (PdoGsb::$monPdoGsb == null) {
            PdoGsb::$monPdoGsb = new PdoGsb();
        }
        return PdoGsb::$monPdoGsb;
    }

     public function insertDemande($nom, $prenom, $email, $objet, $message) {
        $idClient = $this->getIdClientForCreateTicket($nom, $prenom, $email);
        if($idClient==""){
            $req = "insert into client values (NULL,'$nom','$prenom','$email')";
            PdoGsb::$monPdo->exec($req);
            $idClient = $this->getIdClientForCreateTicket($nom, $prenom, $email);
        }
        date_default_timezone_set('Europe/Paris');
        $dateCourante = (String) date('Y-m-d');
        $heureCourante = (String) date("H:i:s");
        $i = '1';
        $req2 = "insert into demande values(NULL,'$dateCourante','$heureCourante','$objet','$message','$idClient','$i')";
        PdoGsb::$monPdo->exec($req2);
        $id_demande = $this->getIdDemandInserted($dateCourante, $heureCourante, $objet, $message);
        $this->insertDemandOnAllOperator($id_demande);
    }

    public function getIdClientForCreateTicket($nom, $prenom, $email) {
        $req = "select id_client from client where nom_client='$nom' and prenom_client='$prenom' and mail_client='$email'";
        $res = PdoGsb::$monPdo->query($req);
        $response = $res->fetch();
        $retour = $response['id_client'];
        return $retour;
    }

    public function insertDemandOnAllOperator($idDemande) {
        $req = "select id_ps "
                . "from personne_support "
                . "where id_role=(select id_role "
                . "from role "
                . "where libelle_role='Operateur')";
        $res = PdoGsb::$monPdo->query($req);
        $set = $res->fetchAll();
        foreach ($set as $unId) {
            $idPs = $unId['id_ps'];
            $req2 = "insert into traiter values('$idDemande','$idPs')";
            PdoGsb::$monPdo->exec($req2);
        }
    }

    public function getIdDemandInserted($dateCourante, $heureCourante, $objet, $message) {
        $req = "select id_demande "
                . "from demande "
                . "where date_demande='$dateCourante' "
                . "and heure_demande='$heureCourante' "
                . "and objet_demande='$objet' "
                . "and detail_demande='$message'";
        $res = PdoGsb::$monPdo->query($req);
        $resultat = $res->fetch();
        $idCurentDemand = $resultat['id_demande'];
        return $idCurentDemand;
    }


    /**
     * Retourne les informations d'un administrateur

     * @param $login 
     * @param $mdp
     * @return l'id, le nom et le prénom sous la forme d'un tableau associatif 
     */
    public function getInfosAdmin($login, $mdp) {
        $req = "select id_ps as id, nom_ps as nom, prenom_ps as prenom from personne_support 
		where login_ps='$login' and mot_de_passe_ps='$mdp'";
        $rs = PdoGsb::$monPdo->query($req);
        $ligne = $rs->fetch();
        return $ligne;
    }

    public function getRoleAdmin($login, $mdp) {
        $req = "select libelle_role from role where id_role in (Select id_role from personne_support where login_ps='$login' and mot_de_passe_ps='$mdp')";
        $rs = PdoGsb::$monPdo->query($req);
        $result = $rs->fetch();
        $role = $result['libelle_role'];
        return $role;
    }

    public function getThemeAdmin($login, $mdp) {
        $req = "select theme.id_theme
				from theme, personne_support 
				where theme.id_theme = personne_support.id_theme
				and personne_support.login_ps = '$login'
				and personne_support.mot_de_passe_ps = '$mdp'";
        $rs = PdoGsb::$monPdo->query($req);
        $result = $rs->fetch();
        $theme = $result['id_theme'];
        return $theme;
    }

    public function getListeDemandes() {
        $req = "select demande.id_demande,date_demande,objet_demande,libelle_etat,nom_ps,prenom_ps "
                . "from demande,personne_support,traiter,etat "
                . "where demande.id_demande=traiter.id_demande "
                . "and traiter.id_ps=personne_support.id_ps  "
                . "and demande.id_etat=etat.id_etat "
                . "and demande.id_etat!=3 ";
        $res = PdoGsb::$monPdo->query($req);
        $lesLignes = $res->fetchAll();
        return $lesLignes;
    }
    
    public function getListeDemandesExpert($nomExpert, $prenomExpert) {
        $req = "select demande.id_demande,date_demande,objet_demande,libelle_etat,nom_ps,prenom_ps "
                . "from demande,personne_support,traiter,etat "
                . "where demande.id_demande=traiter.id_demande "
                . "and traiter.id_ps=personne_support.id_ps  "
                . "and demande.id_etat=etat.id_etat "
                . "and demande.id_etat!=3 "
                . "and nom_ps='$nomExpert' "
                . "and prenom_ps='$prenomExpert' ";
        $res = PdoGsb::$monPdo->query($req);
        $lesLignes = $res->fetchAll();
        return $lesLignes;
    }

    public function getListePersonnesSupport() {
        $req = "select id_ps,nom_ps,prenom_ps,libelle_role "
                . "FROM personne_support,role "
                . "WHERE personne_support.id_role=role.id_role ";
        $rs = PdoGsb::$monPdo->query($req);
        $result = $rs->fetchAll();
        return $result;
    }

    public function getDetailDemande($idDemande) {
        $req = "select id_demande,nom_client,prenom_client,date_demande,heure_demande,mail_client,objet_demande,detail_demande,libelle_etat "
                . "FROM demande,client,etat "
                . "WHERE demande.id_client=client.id_client "
                . "and demande.id_etat=etat.id_etat "
                . "and id_demande='$idDemande' ";
        $res = PdoGsb::$monPdo->query($req);
        $laLigne = $res->fetch();
        return $laLigne;
    }
    
    public function affectPersonForDemand($idDemandeAffect, $idPersonneAffect) {
        $req = "delete from traiter "
                . "where id_demande='$idDemandeAffect'";
        PdoGsb::$monPdo->exec($req);
        $req2 = "insert into traiter values('$idDemandeAffect','$idPersonneAffect')";
        PdoGsb::$monPdo->exec($req2);
    }
    
    public function getInfosDemandesAChanger($idDemandeAchanger) {
        $req = "select id_demande,date_demande,heure_demande "
                . "from demande "
                . "where id_demande='$idDemandeAchanger'";
        $res = PdoGsb::$monPdo->query($req);
        $laLigne = $res->fetch();
        return $laLigne;
    }
    
    public function changerEtatDemande($idDemandeChangement, $commentaire) {
        date_default_timezone_set('Europe/Paris');
        $dateCourante = (String) date('Y-m-d');
        $heureCourante = (String) date("H:i:s");
        $idAncienEtat = $this->getIdEtatBeforeChange($idDemandeChangement);
        $idNouvelEtat = $idAncienEtat + 1;
        $req = "insert into changement_etat values('','$dateCourante','$heureCourante','$commentaire','$idDemandeChangement','$idAncienEtat','$idNouvelEtat') ";
        PdoGsb::$monPdo->exec($req);
        $req2 = "update demande set id_etat='$idNouvelEtat' where id_demande='$idDemandeChangement' ";
        PdoGsb::$monPdo->exec($req2);
    }

    public function getIdEtatBeforeChange($idDemande) {
        $req = "select id_etat "
                . "from demande "
                . "where id_demande='$idDemande' ";
        $res = PdoGsb::$monPdo->query($req);
        $result = $res->fetch();
        $idEtat = $result['id_etat'];
        return $idEtat;
    }

   

    public function getDemandesTotal() {
        $req = "select count(*) from demande";
        $rs = PdoGsb::$monPdo->query($req);
        $result = $rs->fetch();
        $demandes_total = $result['count(*)'];
        return $demandes_total;
    }

    public function getTempsMoyen() {
        $req = "select AVG(DATEDIFF(changement_etat.date_changement, demande.date_demande)) as moyenne
				from changement_etat, demande
				where demande.id_demande = changement_etat.id_demande 
				and id_nouvel_etat = 3";
        $rs = PdoGsb::$monPdo->query($req);
        $result = $rs->fetch();
        $temps_moyen = $result['moyenne'];
        return $temps_moyen;
    }

    public function getDemandesAttente() {
        $req = "select count(*)from demande where id_etat !=3; ";
        $rs = PdoGsb::$monPdo->query($req);
        $result = $rs->fetch();
        $demandes_attente = $result['count(*)'];
        return $demandes_attente;
    }

    public function getDemandesExpert() {
        $req = "select count(*) from traiter, personne_support, role where traiter.id_ps = personne_support.id_ps and personne_support.id_role = role.id_role and role.id_role = 2";
        $rs = PdoGsb::$monPdo->query($req);
        $result = $rs->fetch();
        $demandes_expert = $result['count(*)'];
        return $demandes_expert;
    }

    public function getDemandesOperateur() {
        $req = "select count(*) from traiter, personne_support, role where traiter.id_ps = personne_support.id_ps and personne_support.id_role = role.id_role and role.id_role = 1";
        $rs = PdoGsb::$monPdo->query($req);
        $result = $rs->fetch();
        $demandes_operateur = $result['count(*)'];
        return $demandes_operateur;
    }

   

    public function updateTheme1($nom, $prenom) {
        $req = "update personne_support set id_theme = 1 where prenom_ps ='$prenom' and nom_ps = '$nom';";
        PdoGsb::$monPdo->exec($req);
    }

    public function updateTheme2($nom, $prenom) {
        $req = "update personne_support set id_theme = 2 where prenom_ps ='$prenom' and nom_ps = '$nom';";
        PdoGsb::$monPdo->exec($req);
    }

    public function getThemeAdminUpdate($nom, $prenom) {
        $req = "select theme.id_theme
				from theme, personne_support 
				where theme.id_theme = personne_support.id_theme
				and personne_support.prenom_ps ='$prenom' and personne_support.nom_ps = '$nom';";
        $rs = PdoGsb::$monPdo->query($req);
        $result = $rs->fetch();
        $theme = $result['id_theme'];
        return $theme;
    }

  

    public function getIdEtat($idDemande) {
        $req = "select id_etat from demande where id_demande = '$idDemande'";
        $res = PdoGsb::$monPdo->query($req);
        $response = $res->fetch();
        $retour = $response['id_etat'];
        return $retour;
    }

    public function getListeDemandesRechercheNom($nom) {
        $req = "select id_demande as id,date_demande as date,objet_demande as objet from demande where id_client IN(Select id_client from client where nom_client='$nom' )";
        $res = PdoGsb::$monPdo->query($req);
        $lesLignes = $res->fetchAll();
        return $lesLignes;
    }

    public function getListeDemandesRechercheId($numero) {
        $req = "select id_demande as id,date_demande as date,objet_demande as objet from demande where id_demande = '$numero'";
        $res = PdoGsb::$monPdo->query($req);
        $lesLignes = $res->fetchAll();
        return $lesLignes;
    }

    public function getListeDemandesRechercheCroisee($nom, $numero) {
        $req = "select id_demande as id,date_demande as date,objet_demande as objet from demande where id_client IN(Select id_client from client where nom_client='$nom' ) and id_demande ='$numero'";
        $res = PdoGsb::$monPdo->query($req);
        $lesLignes = $res->fetchAll();
        return $lesLignes;
    }

}
